Unique Constraint VS Unique Index

While reviewing my previous article on SQL Server Performance Tuning, I realized that the prefixes for indexes were incomplete. After referring to the article "The Prefixes for Indexes and Constraints You Need to Understand (AK, PK, IX, CK, FK, DF, UQ)", I have added the following:

• Primary Key: PK_TableName.
• Clustered Index: CX_TableName_Column1_Column2.
• Non-Clustered Index: IX_TableName_Column1_Column2.
• Unique Index (Alternate Key): AK_TableName_Column1_Column2.
• Unique Constraint: UQ_TableName_Column1_Column2.
• Check Constraint: CK_TableName_Column1_Column2.
• Default Constraint: DF_TableName_Column1_Column2.
• Foreign Key: FK_TableName1_Column1_Column2_TableName2.

After these additions, I noticed that both Unique Constraint and Unique Index are related to data uniqueness.

By definition, a Constraint is used to ensure data integrity, while an Index is used for query performance. However, in practice, a Unique Index can also ensure data uniqueness.

According to the article "Create Unique Constraints", when a Unique Constraint is created, a Unique Index is created simultaneously.

So, what is the point of creating a Unique Constraint? There is a common belief online that Foreign Keys can only reference a Unique Constraint. After all, one of the error messages when a Foreign Key fails to create is "The columns in the table do not match an existing primary key or UNIQUE constraint."

Both Constraints and Unique Indexes can be successful.

CREATE TABLE [dbo].[Main](
 [Id] [uniqueidentifier] NOT NULL,
 [SeqNo] [bigint] IDENTITY(1,1) NOT NULL,
 [UQ] [bigint] NULL,
 [AK] [bigint] NULL,
    CONSTRAINT [PK_Main] PRIMARY KEY NONCLUSTERED (
     [Id] ASC
    ) WITH (
     PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
    ) ON [PRIMARY],
    CONSTRAINT [UQ_Main] UNIQUE NONCLUSTERED (
     [UQ] ASC
    ) WITH (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ref](
 [Id] [bigint] IDENTITY(1,1) NOT NULL,
 [MainId] [uniqueidentifier] NOT NULL,
 [RefSeqNo] [bigint] NOT NULL,
 [RefUQ] [bigint] NULL,
 [RefAK] [bigint] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Ref]  WITH CHECK ADD  CONSTRAINT [FK_Ref_Main] FOREIGN KEY([MainId])
REFERENCES [dbo].[Main] ([Id])
GO
ALTER TABLE [dbo].[Ref] CHECK CONSTRAINT [FK_Ref_Main]
GO
ALTER TABLE [dbo].[Ref]  WITH CHECK ADD  CONSTRAINT [FK_Ref_Main_AK] FOREIGN KEY([RefAK])
REFERENCES [dbo].[Main] ([AK])
GO
ALTER TABLE [dbo].[Ref] CHECK CONSTRAINT [FK_Ref_Main_AK]
GO
ALTER TABLE [dbo].[Ref]  WITH CHECK ADD  CONSTRAINT [FK_Ref_Main_UQ] FOREIGN KEY([RefUQ])
REFERENCES [dbo].[Main] ([UQ])
GO
ALTER TABLE [dbo].[Ref] CHECK CONSTRAINT [FK_Ref_Main_UQ]
GO

Furthermore, as noted in the article "Create Unique Indexes", in SQL Server, a Unique Constraint is primarily more semantically explicit.

There is no significant difference between creating a UNIQUE constraint and creating a unique index independent of a constraint. Data validation occurs in the same way, and the query optimizer does not differentiate between a unique index created by a constraint or manually created. However, creating a UNIQUE constraint on a column makes the index objective clearer.

Creating Unique Constraint and Unique Index

Creating via SQL

To create a Unique Constraint using SQL, replace {} with the corresponding content.

ALTER TABLE {TableName} ADD CONSTRAINT {IndexName} UNIQUE ({ColumnName});
CREATE UNIQUE INDEX {IndexName} ON {TableName} ({ColumnName});

-- Example: ALTER TABLE Main ADD CONSTRAINT UQ_Main_UQ UNIQUE (UQ);

To create a Unique Index using SQL, replace {} with the corresponding content.

CREATE UNIQUE INDEX {IndexName} ON {TableName} ({ColumnName});

-- Example: CREATE UNIQUE INDEX AK_Main_AK ON Main (AK);

Creating via SSMS

1. Open the table designer, right-click and select [Indexes/Keys].
2. Choose the settings as needed:
   • Unique Constraint:
     • Type: Unique Key.
     • Is Unique: Yes (this will be selected automatically and will be grayed out).
   • Unique Index:
     • Type: Index.
     • Is Unique: Yes.

TIP

If you are simply creating an index, you can also right-click the "Indexes" folder and select "New Index". Note that if the table designer is open, "New Index" will be grayed out and unavailable.

SSMS Display

How do you know if you created a Unique Constraint or a Unique Index?

In the SSMS object explorer, the "Keys" folder displays indexes related to constraints, such as Primary Key, Unique Constraint, and Foreign Key. The "Indexes" folder displays all indexes except for Foreign Keys.

Change Log

• 2024-07-25 Initial document creation.